import pymysql
from pymysql.cursors import DictCursor
import logging

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger('MyORM')

class DBConnection:
    """数据库连接管理类，使用单例模式确保连接复用"""
    _instance = None
    
    def __new__(cls, *args, **kwargs):
        if cls._instance is None:
            cls._instance = super(DBConnection, cls).__new__(cls)
            cls._instance.initialized = False
        return cls._instance
    
    def __init__(self, host="192.168.1.129", port=3306, user="admin1", 
                 password="123", database="mumushouji", charset="utf8"):
        if not self.initialized:
            try:
                self.conn = pymysql.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    database=database,
                    charset=charset,
                    cursorclass=DictCursor,
                    autocommit=True
                )
                self.initialized = True
                logger.info("数据库连接成功")
            except Exception as e:
                logger.error(f"数据库连接失败: {e}")
                raise
    
    def get_cursor(self):
        """获取游标"""
        try:
            return self.conn.cursor()
        except Exception as e:
            logger.error(f"获取游标失败: {e}")
            # 如果连接断开，尝试重新连接
            self.initialized = False
            self.__init__()
            return self.conn.cursor()
    
    def close(self):
        """关闭连接"""
        if hasattr(self, 'conn') and self.conn:
            self.conn.close()
            logger.info("数据库连接已关闭")


class MyORM:
    def __init__(self):
        # 使用单例模式的数据库连接
        self.db = DBConnection()
        self.cursor = self.db.get_cursor()
    
    def execute(self, sql, params=None):
        """执行SQL语句，支持参数化查询"""
        try:
            logger.info(f"执行SQL: {sql}, 参数: {params}")
            self.cursor.execute(sql, params)
            
            # 根据SQL类型返回不同的结果
            if sql.strip().upper().startswith('SELECT'):
                return self.cursor.fetchall()
            elif sql.strip().upper().startswith('INSERT'):
                # 返回最后插入的ID
                return {'last_id': self.cursor.lastrowid, 'affected_rows': self.cursor.rowcount}
            else:
                # UPDATE, DELETE等操作返回影响的行数
                return {'affected_rows': self.cursor.rowcount}
        except Exception as e:
            logger.error(f"SQL执行错误: {e}")
            raise


class Model:
    def __init__(self, **kwargs):
        # 保存实例属性
        for k, v in kwargs.items():
            self.__setattr__(k, v)
        
        # 初始化ORM实例，确保共享同一个数据库连接
        self.orm = MyORM()
    
    def field(self, select_params):
        """指定查询的列"""
        if not isinstance(select_params, list):
            raise TypeError("select_params必须是列表类型")
        
        # 防止SQL注入，验证字段名是否合法
        safe_params = []
        for param in select_params:
            # 简单验证字段名是否只包含字母、数字和下划线
            if not all(c.isalnum() or c == '_' for c in param):
                raise ValueError(f"不合法的字段名: {param}")
            safe_params.append(param)
        
        self.columns = ','.join(safe_params)
        return self
    
    def query(self, limit=None, **where_params):
        """查询数据,支持参数化查询和灵活的limit"""
        table = self.__class__.__getattribute__(self, 'table_name')
        
        # 构建SELECT语句
        if hasattr(self, 'columns'):
            sql = f"SELECT {self.columns} FROM {table}"
        else:
            sql = f"SELECT * FROM {table}"
        
        # 处理WHERE条件
        params = []
        if where_params:
            where_clauses = []
            for k, v in where_params.items():
                # 验证字段名是否合法
                if not all(c.isalnum() or c == '_' for c in k):
                    raise ValueError(f"不合法的字段名: {k}")
                where_clauses.append(f"{k} = %s")
                params.append(v)
            
            sql += " WHERE " + " AND ".join(where_clauses)
        
        # 添加LIMIT子句
        if limit is not None:
            sql += f" LIMIT {int(limit)}"
        
        # 执行查询
        return self.orm.execute(sql, params)
    
    def insert(self):
        """插入数据，支持参数化查询"""
        table = self.__class__.__getattribute__(self, 'table_name')
        
        # 过滤掉orm属性和内部属性
        data = {k: v for k, v in self.__dict__.items() 
                if k != 'orm' and not k.startswith('_') and not callable(v)}
        
        if not data:
            raise ValueError("没有数据可插入")
        
        # 构建INSERT语句
        keys = list(data.keys())
        placeholders = ["%s"] * len(keys)
        values = [data[k] for k in keys]
        
        # 验证字段名是否合法
        for k in keys:
            if not all(c.isalnum() or c == '_' for c in k):
                raise ValueError(f"不合法的字段名: {k}")
        
        sql = f"INSERT INTO {table} ({','.join(keys)}) VALUES ({','.join(placeholders)})"
        
        # 执行插入
        return self.orm.execute(sql, values)


class User(Model):
    table_name = "user"
    
    def __init__(self, **kwargs):
        super().__init__(**kwargs)


class Article(Model):
    table_name = "article"


if __name__ == "__main__":
    try:
        # 插入用户示例
        user = User(
            username="dazhoulaoshi1",
            password="1231",
            nickname="大周老师1",
            picture="2.jpg",
            job="全栈工程师"
        )
        result = user.insert()
        print(f"插入用户结果: {result}")
        
        # 查询用户示例
        user = User()
        print("查询所有用户:")
        print(user.query())
        
        print("查询指定字段:")
        print(user.field(["user_id", "username", "nickname"]).query())
        
        print("条件查询:")
        print(user.field(["user_id", "username", "nickname"]).query(user_id=3, username="3@qq.com"))
        print(user.query(user_id=3, username="3@qq.com"))
        
        # 查询文章示例
        article = Article()
        print("查询所有文章:")
        print(article.query())
        
        print("条件查询文章:")
        print(article.field(["label_name", "title"]).query(id=5))
        
        # 关闭数据库连接
        DBConnection().close()
    except Exception as e:
        logger.error(f"程序执行错误: {e}")
        # 确保关闭数据库连接
        DBConnection().close() 